package com.sq.partner.manager.service.impl;

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.stereotype.Service;

import com.sq.partner.manager.common.Config;
import com.sq.partner.manager.common.Result;
import com.sq.partner.manager.common.exception.AppException;
import com.sq.partner.manager.dao.ICoreDeptDao;
import com.sq.partner.manager.dao.IGatewayAgentInfoMtDao;
import com.sq.partner.manager.dao.IGatewayAppDao;
import com.sq.partner.manager.dao.IGatewayPolicyDao;
import com.sq.partner.manager.dao.IGatewayTransDao;
import com.sq.partner.manager.dao.ITradNoCarProdPlanDao;
import com.sq.partner.manager.mybatis.model.CoreDept;
import com.sq.partner.manager.mybatis.model.GatewayManual;
import com.sq.partner.manager.mybatis.model.GatewayPolicy;
import com.sq.partner.manager.mybatis.model.GatewayTrans;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.NocarPlan;
import com.sq.partner.manager.mybatis.model.TradAgentInfo;
import com.sq.partner.manager.service.IGatewayDataManageService;
import com.sq.partner.manager.util.ExcelUtil;
import com.sq.partner.manager.util.HttpConnectorUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.StrUtil;
@Service
public class GatewayDataManageServiceImpl implements IGatewayDataManageService {
    private static Logger logger = LogUtil.getLog(GatewayDataManageServiceImpl.class);
	
	@Resource
	private IGatewayAppDao appDao;
	@Resource
	private IGatewayTransDao transDao;
	@Resource
	private ICoreDeptDao coreDeptDao;
	@Resource
	private ITradNoCarProdPlanDao noCarProdPlanDao;
	@Resource
	private IGatewayAgentInfoMtDao agentInfoMtDao;
	@Resource
	private IGatewayPolicyDao   gatewayPolicyDao;
	@Override
	public List<CoreDept> queryDeptList(GatewayUser user) throws Exception {
		return coreDeptDao.queryDeptList(user.getDepartmentStr());
	}

	@Override
	public Page queryTransByCriteria(GatewayTrans trans, Integer pageIndex,
			GatewayUser user) throws Exception {
		Page transPage = transDao.queryByCriteria(trans, user, pageIndex);
		return transPage;
	}
	@Override
	public Map queryTransMapByCriteria(GatewayTrans trans, Integer pageIndex,
			GatewayUser user)throws Exception {
		return transDao.queryzMapByCriteria(trans, user, pageIndex);
	}
	@Override
	public List<GatewayManual> readReconciliationXls(InputStream inputStream) throws Exception {
		
		HSSFWorkbook workBook = null;
		
		try{
			workBook = new HSSFWorkbook(inputStream);
		}catch(Exception e){
			logger.info("对账文件格式有误:不是标准的xls文件",e);
			throw new AppException("对账文件格式有误:不是标准的xls文件");
		}
		
		List<GatewayManual> list = new ArrayList<GatewayManual>();
		
		boolean flag = true;
		
		for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
			HSSFSheet sheet = workBook.getSheetAt(numSheet);
			
			// Read the Row
			for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
				HSSFRow row = sheet.getRow(rowNum);
				if (row != null) {
					flag = false;
					GatewayManual manual = new GatewayManual();
					
					HSSFCell channelCodeCell = row.getCell(0);
					HSSFCell channelComcodeCell = row.getCell(1);
					HSSFCell trxDateCell = row.getCell(2);
					HSSFCell trxCodeCell = row.getCell(3);
					HSSFCell polnoCell = row.getCell(4);
					HSSFCell confirmnoCell = row.getCell(5);
					HSSFCell premiumCell = row.getCell(6);
					
					if(channelCodeCell == null){
						throw new AppException("对账文件格式有误:“"+workBook.getSheetName(numSheet)+"”工作表第"+(rowNum+1)+"行中“合作伙伴代码”不能为空");
					}
					if(trxCodeCell == null){
						throw new AppException("对账文件格式有误:“"+workBook.getSheetName(numSheet)+"”工作表第"+(rowNum+1)+"行中“交易代码”不能为空");
					}
					if(polnoCell == null){
						throw new AppException("对账文件格式有误:“"+workBook.getSheetName(numSheet)+"”工作表第"+(rowNum+1)+"行中“保单号”不能为空");
					}
					if(premiumCell == null){
						throw new AppException("对账文件格式有误:“"+workBook.getSheetName(numSheet)+"”工作表第"+(rowNum+1)+"行中“含税保费”不能为空");
					}
					
					String channelCode = ExcelUtil.getCellText(channelCodeCell);
					String channelComcode = ExcelUtil.getCellText(channelComcodeCell);
					String trxcode = ExcelUtil.getCellText(trxCodeCell);
					String polno = ExcelUtil.getCellText(polnoCell);
					String confirmno = ExcelUtil.getCellText(confirmnoCell);
					
					try{
						String primium = ExcelUtil.getCellText(premiumCell);
						manual.setPremium(new BigDecimal(primium).setScale(2,BigDecimal.ROUND_HALF_UP));
						
					}catch(NumberFormatException n){
						logger.info("对账文件格式有误",n);
						throw new AppException("对账文件格式有误:“"+workBook.getSheetName(numSheet)+"”工作表第"+(rowNum+1)+"行中“含税保费”应为数值类型");
					}
					
					
					if(trxDateCell != null){
						Date trxdate = ExcelUtil.getCellDate(trxDateCell);
						manual.setTrxdate(trxdate);
					}
					
					if(!trxcode.equals("2011") && !trxcode.equals("1021") && !trxcode.equals("1022") && !trxcode.equals("T01") && !trxcode.equals("T05")){
						throw new AppException("对账文件格式有误:“"+workBook.getSheetName(numSheet)+"”工作表第"+(rowNum+1)+"行中“交易代码”错误");
					}else if(trxcode.equals("1021")||trxcode.equals("2011")||trxcode.equals("T01")){
						if(StrUtil.isEmpty(polno)){
							throw new AppException("对账文件格式有误:“"+workBook.getSheetName(numSheet)+"”工作表第"+(rowNum+1)+"行中“保单号”不能为空");
						}else if(StrUtil.isNotEmpty(confirmno)){
							throw new AppException("对账文件格式有误:“"+workBook.getSheetName(numSheet)+"”工作表第"+(rowNum+1)+"行中“批单号”应为空");
						}
					}else if(trxcode.equals("1022")||trxcode.equals("T05")){
						if(StrUtil.isEmpty(confirmno)){
							throw new AppException("对账文件格式有误:“"+workBook.getSheetName(numSheet)+"”工作表第"+(rowNum+1)+"行中“批单号”不能为空");
						}
					}
					
					manual.setChannelCode(channelCode);
					manual.setChannelComcode(channelComcode);
					manual.setTrxcode(trxcode);
					manual.setPolno(polno);
					manual.setConfirmno(confirmno);
					
					list.add(manual);
				}
			}
		}
		
		if(flag){
			throw new AppException("对账文件数据为空");
		}
		
		return list;
	}

	@Override
	public Map<String, Integer> reconciliation(List<GatewayManual> manualList,GatewayUser user)
			throws Exception {

		int total = 0;//对账总数
		int success = 0;//对账成功
		int error = 0;//对账失败
		//added  by gzz@20150817 for fixed bug begin 先检测是否有权限查询，若有权限则对账，若没有则提示没有权限
		String channelCodeCheckStr="";
		//1.拼接合作伙伴串，完成检查条件
		for(GatewayManual manual : manualList){
			if(!channelCodeCheckStr.contains(manual.getChannelCode())){
				channelCodeCheckStr = manual.getChannelCode()+",";
			}
		}
		//2.检查数据库，得到有权限的合作伙伴code
		List<TradAgentInfo> allChannelInfo = agentInfoMtDao.searchExistChannelInfoFromDB(user.getDepartmentStr());
		if(null!=allChannelInfo && allChannelInfo.size()>0){
			//存取检查对象
			List<String> temChannelLs = new ArrayList<String>();
			for(TradAgentInfo tem :allChannelInfo){
				temChannelLs.add(tem.getChannelCode());
			}
			String[] StrArray = channelCodeCheckStr.substring(0,channelCodeCheckStr.length()-1).split(",");
			for(String tem :StrArray){
				if(!temChannelLs.contains(tem)){
					throw new AppException("合作伙伴代码 "+tem+" 不存在或没有相关权限，请全列检查修改");
				}
			}
		}else{
			throw new AppException("当前登陆用户权限下不存在任何合作伙伴信息，请先申请权限后后导入");
		}
		allChannelInfo=null;
		channelCodeCheckStr="";//清空str
		//added  by gzz@20150817 for fixed bug end 
		for (GatewayManual manual : manualList) {
			total++;
			
			String channelCodeExcel = manual.getChannelCode();
			String channelComcodeExcel = manual.getChannelComcode();
			Date trxdateExcel = manual.getTrxdate();
			String trxcodeExcel = manual.getTrxcode();
			String polnoExcel = manual.getPolno();
			String confirmnoExcel = manual.getConfirmno();
			BigDecimal premiumExcel = manual.getPremium();
			
			GatewayTrans transExcel = new GatewayTrans();
			List<GatewayTrans> transList = new ArrayList<GatewayTrans>();
			logger.info("手动对账导入excel交易类型："+trxcodeExcel);
			if("1021".equals(trxcodeExcel) || "2011".equals(trxcodeExcel) ||"T01".equals(trxcodeExcel)){//投保交易
				transExcel.setTrxcode(trxcodeExcel);
				transExcel.setPolno(polnoExcel);
				transList = transDao.queryListByTransAndDept(user, transExcel);
			}else if("1022".equals(trxcodeExcel) || "T05".equals(trxcodeExcel)){//批改交易
				transExcel.setTrxcode(trxcodeExcel);
				transExcel.setPolno(polnoExcel);
				transExcel.setConfirmno(confirmnoExcel);
				transList = transDao.queryListByTransAndDept(user, transExcel);
			}
			
			if(transList.size() == 1){
				
				GatewayTrans trans = transList.get(0);
				Integer checkFlag = trans.getCheckFlag();
				if(checkFlag != -1 && checkFlag != 1 && checkFlag != 3){//判断对账状态不等于-1、1和3的时候才进行状态修改
					//核对金额
					logger.info("手动对账导入excel中保费："+premiumExcel);
					logger.info("手动对账数据库查询保费："+trans.getPremium().setScale(2,BigDecimal.ROUND_HALF_UP));
					if(null != premiumExcel && null != trans.getPremium().setScale(2,BigDecimal.ROUND_HALF_UP) && premiumExcel.equals(trans.getPremium().setScale(2,BigDecimal.ROUND_HALF_UP))){
							trans.setCheckFlag(1);//对账成功 状态更新为 1 
							trans.setUpdateTime(new Date());
							boolean res = transDao.updateNotNull(trans);//记录到数据库
							if(res){
								success++;
							}else{
								error++;
							}
					}else{
						logger.info("对账金额有差异，对账失败");
						error++;
						
						trans.setCheckFlag(2);//对账失败 状态更新为2
						trans.setUpdateTime(new Date());
						transDao.updateNotNull(trans);//记录到数据库
					}
					
				}else if(checkFlag == 3){
					error++;
				}else{
					success++;
				}
			}else if(transList.size() == 0){//对账数据多于数据库中时此条数据需要添加到数据库中  状态为3
				error++;
				logger.info("对账数据多于数据库数据添加到数据库中");
				GatewayTrans tr = new GatewayTrans();
				tr.setTransid("Unknown"+UUID.randomUUID().toString());;//生成UUID主键
				tr.setChannelCode(channelCodeExcel);
				tr.setChannelComcode(channelComcodeExcel);
				tr.setTrxdate(trxdateExcel);
				tr.setTrxcode(trxcodeExcel);
				tr.setPolno(polnoExcel);
				tr.setConfirmno(confirmnoExcel);
				tr.setPremium(premiumExcel);
				tr.setCheckFlag(3);
				tr.setInsertTime(new Date());
				tr.setUpdateTime(new Date());
				tr.setOperator(user.getUserCode());
				
				transDao.save(tr);
			}else{
				logger.info("excel导入对账数据其中一个保单号对应数据库多条错误："+transList.size());
				error++;
			}
		}
		
		Map<String,Integer> resultMap = new HashMap<String, Integer>();
		resultMap.put("total", total);
		resultMap.put("success",success);
		resultMap.put("error",error);
		
		return resultMap;
	}

	@Override
	public Page<CoreDept> queryPageForCoreDept(Page<CoreDept> page,
			String parentCode) throws Exception {
		// TODO Auto-generated method stub
		return coreDeptDao.queryCoreDeptPageByParentCode(page, parentCode);
	}

	@Override
	public Page<NocarPlan> queryPageForInsurePlanList(Page<NocarPlan> page,
			String channelCode) throws Exception {
		// TODO Auto-generated method stub
		return noCarProdPlanDao.queryPageForInsurePlanList(page,channelCode);
	}

	@Override
	public Result reconciled(GatewayTrans gatewayTrans) throws Exception {
		// TODO Auto-generated method stub
		boolean res = transDao.updateNotNull(gatewayTrans);//记录到数据库
		if(res){
			return Result.success("对账成功");
		}else{
			return Result.error("对账失败");
		}
	}

	@Override
	public Result policyCancel(GatewayTrans gatewayTrans) throws Exception {
		/**发送请求注销**/
		List<GatewayTrans> list=transDao.queryListByTrans(gatewayTrans);
		if(list!=null&&list.size()>0){
			gatewayTrans=list.get(0);
		}
		/**查询保费*/
		GatewayPolicy gatewayPolicy=gatewayPolicyDao.queryNocarPolicyInfoByPolNo(gatewayTrans.getPolno());
		if(gatewayPolicy==null){
			return Result.error("查询保单信息错误");
		}
		gatewayTrans.setPremium(gatewayPolicy.getPremium());
		return querySauserAndTeamInfo(gatewayTrans);
	}
	public Result querySauserAndTeamInfo(GatewayTrans gatewayTrans)
			throws Exception {
		String url ="";
		/**生成投保序列号*/
		String serialNumber="manager";
		SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmssSSS");
		SimpleDateFormat formatter1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String formatStr =formatter.format(new Date());
		serialNumber=serialNumber+formatStr+UUID.randomUUID().toString().substring(0, 4);
		String transCode="";
		String reqXml = "";
		if(StrUtil.isEmpty(gatewayTrans.getChannelComcode())){
			gatewayTrans.setChannelComcode(gatewayTrans.getChannelCode());
		}
		//拼接请求报文
		if("nocar".equals(gatewayTrans.getAppId())){
			transCode="2014";
			url= Config.getProp("NOCAR.TRANS.URL");
			 reqXml = 
					"<?xml version=\"1.0\" encoding=\"GBK\"?>" +
						"<INSUREQ>" +
							"<HEAD>" +
								"<TRANSRNO>2014</TRANSRNO>" +
								"<PARTNERCODE>"+gatewayTrans.getChannelCode()+"</PARTNERCODE>" +
								"<PARTNERSUBCODE>"+gatewayTrans.getChannelComcode()+"</PARTNERSUBCODE>" +
							"</HEAD>"+
							"<MAIN>"+
								"<SERIALNUMBER>"+serialNumber+"</SERIALNUMBER>" +
								"<TRANSRDATE>"+formatter1.format(new Date())+"</TRANSRDATE>" +
								"<POLNO>"+gatewayTrans.getPolno()+"</POLNO>" +
								"<PREMIUM>"+gatewayTrans.getPremium()+"</PREMIUM>" +
							"</MAIN>"+
						"</INSUREQ>";
		}else if("nlife".equals(gatewayTrans.getAppId())){
			transCode="CIC005";
			url= Config.getProp("NLIFE.TRIAL.URL");
			 reqXml ="<?xml version=\"1.0\" encoding=\"GBK\"?>" +
							"<INSUREQ>" +
								"<HEAD>" +
									"<TRANSRNO>"+transCode+"</TRANSRNO>" +
									"<PARTNERCODE>"+gatewayTrans.getChannelCode()+"</PARTNERCODE>" +
									"<PARTNERSUBCODE>"+gatewayTrans.getChannelComcode()+"</PARTNERSUBCODE>" +
								"</HEAD>"+
								"<MAIN>"+
									"<PRODUCTCODE>"+gatewayTrans.getChannelProductcode()+"</PRODUCTCODE>" +
									"<SERIALNUMBER>"+serialNumber+"</SERIALNUMBER>" +
									"<TRANSRDATE>"+formatter1.format(new Date())+"</TRANSRDATE>" +
									"<POLNO>"+gatewayTrans.getPolno()+"</POLNO>" +
									"<PREMIUM>"+gatewayTrans.getPremium()+"</PREMIUM>" +
									"<CORRECTCODE>19</CORRECTCODE>"+
								"</MAIN>"+
								"<HEALCORRECTINFO></HEALCORRECTINFO>"+
							"</INSUREQ>" ;
		}else{
			return Result.error("此保险不支持注销");
		}
		
		logger.info("保单注销发送交易:"+reqXml);
		
		String respXml = "";
		try{
		    //发送交易系统
			respXml = HttpConnectorUtil.interaction(url, reqXml, gatewayTrans.getChannelCode(), transCode);
			logger.info("交易系统应答"+respXml);
		}catch(Exception e){
			logger.info("调用交易系统异常:",e);
			return Result.error("调用交易系统异常:");
		}
		
		// 解析响应报文
		Document document=null;
		try {
			document = DocumentHelper.parseText(respXml);
		} catch (DocumentException e) {
			logger.info("解析交易系统返回报文异常",e);
			return Result.error("解析交易系统返回报文异常");
		}
		Element root  = document.getRootElement();
		Element RETURN =null;
		String RETURNCODE = "";
		String ERRINFO = "";
		Element main =null;
		String EDRNO = "";
		if("nocar".equals(gatewayTrans.getAppId())){
		     RETURN= root.element("HEAD");  
		     RETURNCODE=RETURN.elementTextTrim("RESULTCODE");
		     ERRINFO=RETURN.elementTextTrim("ERRINFO");
		     main= root.element("MAIN");
		     EDRNO= main.elementTextTrim("EDRNO");
		}else if("nlife".equals(gatewayTrans.getAppId())){
		     RETURN= root.element("HEAD");  
		     RETURNCODE=RETURN.elementTextTrim("RESULTCODE");
		     ERRINFO=RETURN.elementTextTrim("ERRINFO");
		     main= root.element("MAIN");
		     EDRNO= main.elementTextTrim("CONFIRMNO");
		}
		if("0000".equals(RETURNCODE)||"00".equals(RETURNCODE)){
			return Result.success("注销成功,批单号"+EDRNO);
		}else{
			return Result.error("调取交易系统失败:RETURNCODE:" + RETURNCODE +
					"，错误信息:"+ERRINFO);
		} 
	}
}
